<?php
	class ReportController extends Controller{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Finance";
		public $subMenu = "";
		public $title = "";
		public $loginUser = "";
		
		public function actionIndex(){
			$data= Array();
			$arrDept = MDepartment::model()->findAll("is_active = 1");
			$data["arrDept"] = $arrDept;
			$this->layout ="//layouts/ly_blank";
			$this->render('index',$data);
		}
		
		public function actionBCA_VA(){
			$data= Array();
			//echo "Login user </br>";
			//print_r($this->loginUser);
			///echo "</br> TITLE :" . $this->title;
			$this->layout ="//layouts/ly_blank";
			$this->render('bca_va',$data);
		}
		
		public function actionBCA_VADetailList(){
			$period = Yii::app()->request->getParam("period","");
			try{
				
				if ($period == ""){
					throw new Exception("Invalid Period");
				}
				
				$startDate = Utils::getPeriodStartDate($period);
				$endDate = Utils::getPeriodEndDate($period);
				/*
				$ssql = "select a.id,a.student_id,b.sid,b.name as student_name,b.department_id,b.ref_payment,sum(a.ttl_invoice -a.ttl_disc -a.ttl_paid -a.ttl_cn) as ttl_amount  from m_student_hinvoice a
						inner join m_student b on a.student_id = b.id  
						where invoice_date >= :startDate and invoice_date <= :endDate 
						and payment_type = 'BCA VA' 
						and a.is_active = 1 
						and (ttl_invoice - ttl_disc) > (ttl_paid + ttl_cn)
						group by a.student_id";
				*/
				$ssql = "select a.id,a.student_id,b.sid,b.name as student_name,b.department_id,b.bca_va_no,sum(a.ttl_invoice -a.ttl_disc -a.ttl_paid -a.ttl_cn) as ttl_amount  from m_student_hinvoice a
						inner join m_student b on a.student_id = b.id
						where invoice_date <= :endDate
						and payment_type = 'BCA VA'
						and a.is_active = 1
						and (ttl_invoice - ttl_disc) > (ttl_paid + ttl_cn)
						group by a.student_id";
				$conn= Yii::app()->db;
				
				$command = $conn->createCommand($ssql);
				//$rs = $command->queryAll(true,array(":startDate"=> $startDate,":endDate"=>$endDate));
				$rs = $command->queryAll(true,array(":endDate"=>$endDate));
				
				$dataModel= array();
				foreach ($rs as $rw){
					
					$tmp = array();
					$tmp["id"] = $rw["id"];
					$tmp["sid"] = $rw["sid"];
					$tmp["student_name"] = $rw["student_name"];
					$tmp["BCA_VA"] = $rw["bca_va_no"];
					$tmp["ttl_amount"] = $rw["ttl_amount"];
					$tahunAjaranActive = CMTahunAjaran::getTahunAjaranActive($rw["department_id"]);
					$class = CMStudent::getActiveClass($tahunAjaranActive->id,$rw["student_id"]);
					$tmp["class_name"] = $class["kelas_name"];
					//$tmp["class_name"] = "test";
					$dataModel[] = $tmp;
					
					//print_r($tmp);
					//echo "</br></br>";
					
				}
				$this->result["model"] =$dataModel; 				
			}catch (Exception $e){
				//$result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());
				$this->result["actionStatus"] = 1;
				$this->result["actionMessage"] = "";
				$this->result["actionError"] = $e->getMessage();
			}
			
			$this->returnJSON($this->result);
			
			//return;
			//$data= Array();
			//$this->layout ="//layouts/ly_blank";
			//$this->render('bca_va',$data);
		
				
		}
		public function actionBCA_VA2Excel(){
			$strArrSelectedData = Yii::app()->request->getParam("arrSelectedData","");			
			$period = Yii::app()->request->getParam("period","");
			
			$arrSelectedData  =json_decode($strArrSelectedData);
			
			Yii::import('ext.phpexcel.XPHPExcel');
			$objPHPExcel= XPHPExcel::createPHPExcel();
			
			$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
			->setLastModifiedBy("Maarten Balliauw")
			->setTitle("Office 2007 XLSX Test Document")
			->setSubject("Office 2007 XLSX Test Document")
			->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
			->setKeywords("office 2007 openxml php")
			->setCategory("Test result file");
			
			
			// Add some data
			$objPHPExcel->setActiveSheetIndex(0);
			
			$myWorkSheet = $objPHPExcel->getActiveSheet();
			$myWorkSheet->setCellValue('A1', 'Nomor VA')
			->setCellValue('B1', 'Nama')
			->setCellValue('C1', 'Monthly Fee')
			->setCellValue('D1', 'Kelas');
			
			$iRow = 1;
			foreach ($arrSelectedData as $selectedData){
				$iRow++;
				$myWorkSheet->getCell('A'.$iRow)->setValueExplicit($selectedData->BCA_VA, PHPExcel_Cell_DataType::TYPE_STRING);				
				$myWorkSheet->setCellValue('B'.$iRow, $selectedData->student_name)
				->setCellValue('C'.$iRow, $selectedData->ttl_amount)
				->setCellValue('D'.$iRow, $selectedData->class_name);				
			}
			
			
			// Rename worksheet
			$objPHPExcel->getActiveSheet()->setTitle('BCA VA List' . $period);
			
			
			// Set active sheet index to the first sheet, so Excel opens this as the first sheet
			$objPHPExcel->setActiveSheetIndex(0);
			// Redirect output to a client’s web browser (Excel5)
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="BCA_VA_' . $period . '.xls"');
			header('Cache-Control: max-age=0');
			// If you're serving to IE 9, then the following may be needed
			header('Cache-Control: max-age=1');
			
			// If you're serving to IE over SSL, then the following may be needed
			header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
			header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
			header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
			header ('Pragma: public'); // HTTP/1.0
			
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			Yii::app()->end();
			
			
			
			exit;
			
		}
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	}
?>